package com.ustcinfo.study.hive.r1.zhangshouxin;

import java.sql.*;




public class HiveConnection {

    // hive链接地址
    private static String url = "jdbc:hive2://nodex:10000/default";

    //hive用户名
    private static String userName = "hive";

    // hive密码
    private static String passwd = "hive";

    // hive 驱动
    private static String driver = "org.apache.hive.jdbc.HiveDriver";

    private static Connection connection = null;
    private static Statement statement = null;
    private static ResultSet resultSet = null;

	// 查询tables sql
    private static String showTableSql = "show tables";

    // 创建内部表 sql "ip", "domain_name", "access_time", "target_ip", "status"
    private static String createSql = "create table dnsrecord(ip String,domain_name String,access_time String,target_ip String,status int)row format delimited fields terminated by '#' ";
    // 加载数据到表sql
    private static String localDataSql = "load data local inpath '/home/ustc/dnsSample' into table dnsrecord ";

    //查询表
    private static String SelectSql = "select * from dnsrecord ";
    //查询不同的domain_name的数目
    private static String countSql = "select count(distinct domain_name) from dnsrecord";

    private static String dropSql = "drop table dnsrecord";
    //建表
    public static void createtable(Connection conn) throws SQLException {
    	 statement = conn.createStatement();
    	 try {
			statement.executeQuery(createSql);
			  // 获取数据库中所有的表
            resultSet = statement.executeQuery(showTableSql);
            while (resultSet.next()) {
                System.out.println(resultSet.getString(1));
            }
         
		} catch (SQLException e) {
			// TODO Auto-generated catch block
		
			System.out.println("表已存在");
		}finally{
    	statement.close();
		}}
   
    //查询整张表
    public static void selecttable(Connection conn) throws SQLException{
    	statement = conn.createStatement();
    	 resultSet = statement.executeQuery(SelectSql);
         while (resultSet.next()) {
             System.out.println(resultSet.getString(1) + "\t" + resultSet.getString(2)+ "\t" + resultSet.getString(3)+ "\t" + resultSet.getString(4)+"\t" + resultSet.getInt(5));
         }
   	statement.close();
    }
    
    //删除表
    public static void droptable(Connection conn) throws SQLException{
    	statement = conn.createStatement();
   	 statement.executeQuery(dropSql);
   	statement.close();
    }
    public static void readdata(Connection conn) throws SQLException{
    	statement = conn.createStatement();
      	 statement.executeQuery(localDataSql);
      	statement.close();
    } public static void count(Connection conn) throws SQLException{
    	statement = conn.createStatement();
     	 statement.executeQuery(countSql);
     	statement.close();
   }
	
    
    public static void main(String[] args) throws SQLException  {
             
        //  加载hive驱动
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            System.out.println("驱动加载失败");
            e.printStackTrace();
        }

        try {
            // 连接hive
            connection = DriverManager.getConnection(url, userName, passwd);
            if (connection != null) {
                System.out.println("hive 连接成功");
            } else {
                System.out.println("hive 连接失败");
            }
            createtable(connection);
        
          selecttable(connection);
          count(connection);
        
        
            connection.close();

   	
           




        } catch (SQLException e) {
            e.printStackTrace();
            System.exit(1);
        } finally {
            if(connection != null) {
                connection.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (resultSet != null) {
                resultSet.close();
            }
        }


    }







}
